﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Microsoft.Win32;
using System.Data.OleDb;
using System.Data;
using System.IO;

namespace AccessCodeGen01
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        private string connStr;

        private List<string> GetTableNameList()
        {
            List<string> list = new List<string>();
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();
                DataTable dt = conn.GetSchema("Tables");
                foreach (DataRow row in dt.Rows)
                {
                    if (row[3].ToString() == "TABLE")
                    {
                        list.Add(row[2].ToString());
                    }
                }
                return list;
            }
        }

        private DataTable ExecuteTableQuery(string tablename)
        {
            string sql = "SELECT TOP 1 * FROM [" + tablename + "]";
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();
                using (OleDbCommand command = new OleDbCommand(sql, conn))
                {
                    //OleDbDataReader reader = command.ExecuteReader();
                    //DataTable dt = reader.GetSchemaTable();
                    OleDbDataAdapter adapter = new OleDbDataAdapter(command);
                    DataSet ds = new DataSet();
                    adapter.FillSchema(ds, SchemaType.Source);
                    adapter.Fill(ds);
                    return ds.Tables[0];
                }
            
            }
        }

        private static string GetDataTypeName(DataColumn column)
        {
            if (column.AllowDBNull && column.DataType.IsValueType)
            {
                return column.DataType.ToString() + "?";
            }
            else
            {
                return column.DataType.ToString();
            }
        }

        //建立Model
        private void GenerateModel(string tablename)
        {
            //DataTable table = ExecuteTableQuery(tablename);
            StringBuilder sb = new StringBuilder();
            sb.Append("public class ").Append(tablename).AppendLine("{");
            DataTable table = ExecuteTableQuery(tablename);
            foreach (DataColumn column in table.Columns)
            {
                string DataTypeName = GetDataTypeName(column);
                sb.Append("public ").Append(DataTypeName).Append(" ").Append(column.ColumnName).AppendLine(" {get;set;}");
            }
            sb.AppendLine("}");
            tbModelCode.Text = sb.ToString();
        }

        /// <summary>
        /// 生成DAL类
        /// </summary>
        /// <param name="tablename"></param>
        private void GenerateDALCode(string tablename)
        {
            ///生成DAL中的ToModel方法
            DataTable table = ExecuteTableQuery(tablename);
            StringBuilder sb = new StringBuilder();
            sb.Append("private ").Append(tablename).AppendLine(" ToModel(DataRow row){");//Model开始
            sb.Append(tablename).Append(" model = new ").Append(tablename).AppendLine("();"); //声明一个Model对象
            foreach (DataColumn column in table.Columns) //列出每个字段的属性对应关系
            {
                sb.Append("model.").Append(column.ColumnName).Append("=(").Append(GetDataTypeName(column)).
                    Append(")SqlHelper.FromDbValue(row[\"").Append(column.ColumnName).AppendLine("\"]);");

            }
            sb.AppendLine("return model;");
            sb.AppendLine("}");//Model结束
            sb.AppendLine("");

            ///生成DAL中的ListAll()方法

            sb.Append("public ").Append(tablename).AppendLine("[] ListAll(){");
            sb.Append("DataTable table = SqlHelper.ExecuteTableQuery(\"SELECT ");
            for (int i = 0; i < table.Columns.Count; i++)
            {
                DataColumn column = table.Columns[i];
                sb.Append("[");
                sb.Append(column.ColumnName);
                sb.Append("]");
                if (i < table.Columns.Count - 1)
                {
                    sb.Append(",");
                }
            }
            sb.Append(" FROM ").Append(tablename).AppendLine("\");");
            sb.Append(tablename).Append("[] models = new ").Append(tablename).AppendLine("[table.Rows.Count];");
            sb.AppendLine("for (int i = 0; i < table.Rows.Count; i++) {");  //ListAll()方法中for循环开始
            sb.AppendLine("models[i]=ToModel(table.Rows[i]);");
            sb.AppendLine("}"); //ListAll()方法中for循环的结束
            sb.AppendLine("return models;");
            sb.AppendLine("}"); //ListAll()方法的结束
            sb.AppendLine("");

            ///生成DAL中的Insert()方法
            //public int Insert(Department dp) {
            sb.Append("public int Insert(").Append(tablename).AppendLine(" model) {");
            //return SqlHelper.ExecuteNonQuery("INSERT INTO T_Department (Id, Name, IsDeleted) VALUES (NEWID(),@Name, 0)",
            //    new OleDbParameter("@Name", dp.Name));
            sb.Append("return SqlHelper.ExecuteNonQuery(\"INSERT INTO ").Append(tablename).Append(" (");
            string[] cols = new string[table.Columns.Count];
            string[] cols2 = new string[table.Columns.Count];
            for (int i = 0; i < table.Columns.Count; i++)
            {
                cols[i] = table.Columns[i].ColumnName;
                cols2[i] = "[" + table.Columns[i].ColumnName + "]";
            }
            string colList = string.Join(",", cols2);
            string paraList = "@" + string.Join(",@", cols);
            sb.Append(colList).Append(") VALUES (").Append(paraList).AppendLine(")\", ");
            for (int i = 0; i < table.Columns.Count; i++)
            {
                DataColumn col = table.Columns[i];
                sb.Append("new OleDbParameter(\"@").Append(col.ColumnName).Append("\",SqlHelper.ToDbValue(model.").Append(col.ColumnName);
                if (i < table.Columns.Count - 1)
                {
                    sb.AppendLine(")),");
                }
                else
                {
                    sb.AppendLine(")));");
                }
            }
            sb.AppendLine("}"); //Insert()方法的结束
            sb.AppendLine("");

            //生成DAL中的DeleteById（）方法
            //    public int DeleteById(Guid Id)
            //{
            //    return SqlHelper.ExecuteNonQuery("DELETE FROM [t_EqAccount] WHERE [Id]=@Id",
            //    new OleDbParameter("@Id", SqlHelper.ToDbValue(Id)));
            //}
            sb.AppendLine("public int DeleteById(int Id) {");
            sb.Append("return SqlHelper.ExecuteNonQuery(\"DELETE FROM [").Append(tablename).AppendLine("] WHERE [Id]=@Id\",");
            sb.AppendLine("    new OleDbParameter(\"@Id\", SqlHelper.ToDbValue(Id)));");
            sb.AppendLine("}"); //DeleteById()方法的结束
            sb.AppendLine("");
            //DeleteById()方法结束

            //生成DAL中的Update()方法
            //    public int Update(Department dp)
            //{
            //    return SqlHelper.ExecuteNonQuery("UPDATE T_Department SET Name=@Name WHERE Id=@Id",
            //        new OleDbParameter("@Name", dp.Name),
            //        new OleDbParameter("@Id", dp.Id));
            //}
            sb.Append("public int Update(").Append(tablename).AppendLine(" model) {");
            sb.Append("return SqlHelper.ExecuteNonQuery(\"UPDATE ").Append(tablename).Append(" SET ");
            for (int i = 1; i < cols.Length; i++)
            {
                sb.Append(cols2[i] + "=@" + cols[i]);
                if (i < cols.Length - 1)
                {
                    sb.Append(",");
                }
            }
            sb.AppendLine(" WHERE [Id]=@Id\",");
            for (int i = 1; i < cols.Length; i++)
            {
                sb.AppendLine("new OleDbParameter(\"@" + cols[i] + "\",SqlHelper.ToDbValue( model." + cols[i] + ")),");
            }
            sb.AppendLine("new OleDbParameter(\"@Id\",SqlHelper.ToDbValue( model.Id)));");
            sb.AppendLine("}"); //Update()方法的结束
            sb.AppendLine("");
            //Update方法结束

            //生成DAL中的GetById()方法
            //public Department GetById(Guid Id){
            sb.Append("public ").Append(tablename).Append(" GetById(int Id){");
            //DataTable table = SqlHelper.ExecuteTableQuery("SELECT Id, Name, IsDeleted FROM T_Department WHERE Id=@Id",
            sb.Append("DataTable table = SqlHelper.ExecuteTableQuery(\"SELECT ").Append(colList).Append(" FROM ").
                Append(tablename).AppendLine(" WHERE [Id]=@Id\",");
            //new OleDbParameter("@Id", Id));
            sb.AppendLine("new OleDbParameter(\"@Id\",SqlHelper.ToDbValue( Id)));");
            //if (table.Rows.Count <= 0){
            sb.AppendLine("if (table.Rows.Count <= 0){");
            //return null;}
            sb.AppendLine("return null;}");
            sb.AppendLine("else if (table.Rows.Count > 1){");
            sb.AppendLine("throw new Exception(\"用户重复！\");}");
            sb.AppendLine("else{");
            sb.AppendLine("DataRow row = table.Rows[0];");
            sb.AppendLine("return ToModel(row);}");
            sb.AppendLine("}"); //GetById()方法的结束
            sb.AppendLine("");

            //生成DAL中的GetByName()方法
            //public Department GetById(Guid Id){
            sb.Append("public ").Append(tablename).Append(" GetByName(string Name){");
            //DataTable table = SqlHelper.ExecuteTableQuery("SELECT Id, Name, IsDeleted FROM T_Department WHERE Id=@Id",
            sb.Append("DataTable table = SqlHelper.ExecuteTableQuery(\"SELECT ").Append(colList).Append(" FROM ").
                Append(tablename).AppendLine(" WHERE [Name]=@Name\",");
            //new OleDbParameter("@Id", Id));
            sb.AppendLine("new OleDbParameter(\"@Name\",SqlHelper.ToDbValue( Name)));");
            //if (table.Rows.Count <= 0){
            sb.AppendLine("if (table.Rows.Count <= 0){");
            //return null;}
            sb.AppendLine("return null;}");
            sb.AppendLine("else if (table.Rows.Count > 1){");
            sb.AppendLine("throw new Exception(\"用户重复！\");}");
            sb.AppendLine("else{");
            sb.AppendLine("DataRow row = table.Rows[0];");
            sb.AppendLine("return ToModel(row);}");
            sb.AppendLine("}"); //GetByName()方法的结束
            sb.AppendLine("");
            tbDALCode.Text = sb.ToString();
        }

        private void GenerateToExcelCode(string tablename)
        {
            //读入标题行文本文件
            string titleLine = tbTxtPath.Text;
            string[] titles;
            if (File.Exists(titleLine))
            {
                string line = File.ReadAllText(titleLine, Encoding.Default);
                titles = line.Split('，');
            }
            else
            {
                MessageBox.Show("标题文件不存在！");
                return;
            }

            StringBuilder sb = new StringBuilder();
            //sb.Append("public class ").Append(tablename).AppendLine("{");
            sb.AppendLine("SaveFileDialog sfd = new SaveFileDialog();");
            sb.AppendLine("sfd.Filter = \"Excel 97-2003文件(*.xls)|*.xls\";");
            sb.AppendLine("string filename;");
            sb.AppendLine("if (sfd.ShowDialog() == true)");
            sb.AppendLine("{");
            sb.AppendLine("    filename = sfd.FileName;");
            sb.AppendLine("");
            sb.Append("    ").Append(tablename).Append("[] models = (").Append(tablename).AppendLine("[])dataGrid.ItemsSource;");

            //    //请Datagrid表中的数据变成DataTable
            //    t_EqAccount[] models = (t_EqAccount[])dgEqAccount.ItemsSource;
            sb.AppendLine("    DataTable table = new DataTable();");
            //    DataTable table = new DataTable();
            for (int i = 0; i < titles.Length; i++)
            {
                sb.Append("    table.Columns.Add(new DataColumn(\"").Append(titles[i]).AppendLine("\"));");
            }
            //    table.Columns.Add(new DataColumn("序号"));
            //    table.Columns.Add(new DataColumn("设备编号"));
            sb.Append("    foreach (").Append(tablename).AppendLine(" model in models)");
            //    foreach (t_EqAccount model in models)
            sb.AppendLine("    {");
            sb.AppendLine("        DataRow row = table.NewRow();");
            DataTable table = ExecuteTableQuery(tablename);
            //foreach (DataColumn column in table.Columns)
            //{
            //    string DataTypeName = GetDataTypeName(column);
            //    sb.Append("public ").Append(DataTypeName).Append(" ").Append(column.ColumnName).AppendLine(" {get;set;}");
            //}
            for (int i = 0; i < titles.Length; i++)
            {
                sb.Append("        row[\"").Append(titles[i]).Append("\"] = model.").Append(table.Columns[i].ColumnName).AppendLine(";");
                
            }

            //        row["序号"] = model.ID;
            //        row["设备编号"] = model.EqCode;

            sb.AppendLine("        table.Rows.Add(row);");
            sb.AppendLine("    }");


            sb.AppendLine("    ExcelHelper.SaveToFile(ExcelHelper.RenderToExcel(table), filename);");
            sb.AppendLine("}");

            tbToExcelCode.Text = sb.ToString();
        }

        private void GenerateXAMLTableCode(string tablename)
        {
            //读入标题行文本文件
            string titleLine = tbTxtPath.Text;
            string[] titles;
            if (File.Exists(titleLine))
            {
                string line = File.ReadAllText(titleLine, Encoding.Default);
                titles = line.Split('，');
            }
            else
            {
                MessageBox.Show("标题文件不存在！");
                return;
            }

            //读入数据库
            DataTable table = ExecuteTableQuery(tablename);

            StringBuilder sb = new StringBuilder();
            sb.AppendLine("<DataGrid Name=\"dgABToolList\" AutoGenerateColumns=\"False\" IsReadOnly=\"True\">");
            sb.AppendLine("    <DataGrid.Columns>");
            for (int i = 0; i < titles.Length; i++)
            {
                sb.Append("        <DataGridTextColumn Header=\"").Append(titles[i]).Append("\" Binding=\"{Binding ").Append(table.Columns[i].ColumnName).AppendLine("}\" Width=\"100\"></DataGridTextColumn>");

            }
        //        <DataGridTextColumn Header=\"订单号\" Binding=\"{Binding OrderNo}\" Width=\"100\"></DataGridTextColumn>
            sb.AppendLine("    </DataGrid.Columns>");
            sb.AppendLine("</DataGrid>");

            tbXAMLCode.Text = sb.ToString();
        
        }
        public MainWindow()
        {
            InitializeComponent();
        }


        private void btnOpen_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Access 97-2003文件(*.mdb)|*.mdb";
            //string filename;
            if (ofd.ShowDialog() == true)
            {
                tbAccessPath.Text = ofd.FileName;
                connStr = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + tbAccessPath.Text + "'";
            }
        }

        private void btnConnect_Click(object sender, RoutedEventArgs e)
        {
            cmbTableList.ItemsSource = GetTableNameList();
            cmbTableList.IsEnabled = true;
            btnGenerateCode.IsEnabled = true;
        }

        private void btnGenerateCode_Click(object sender, RoutedEventArgs e)
        {
            if (cmbTableList.SelectedItem == null)
            {
                MessageBox.Show("请选择表！");
                return;
            }
            string tablename = (string)cmbTableList.SelectedItem;
            GenerateModel(tablename);
            GenerateDALCode(tablename);
            GenerateToExcelCode(tablename);
            GenerateXAMLTableCode(tablename);
        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            //connStr = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source='E:\MyProgram\MyAardio\single\EqInfo.mdb'";
            //cmbTableList.ItemsSource = GetTableNameList();
            //cmbTableList.IsEnabled = true;
            //btnGenerateCode.IsEnabled = true;
            //cmbTableList.SelectedIndex = 2;
        }

        private void btnOpenTxt_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "文本文件(*.txt)|*.txt";
            //string filename;
            if (ofd.ShowDialog() == true)
            {
                tbTxtPath.Text = ofd.FileName;
                
            }
        }
    }
}
